package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import www.lagou.entity.Account;
import www.lagou.entity.Employee;
import www.lagou.entity.Phone;
import www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class Test01 {
    @Test
    public void test1() throws SQLException {
        // 1.自动创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 2.编写sql
        String sql1 = "select * from account where card = ?";
        // 执行查询
        Account account = qr.query(sql1, new BeanHandler<>(Account.class), "1122334455");
        // 获取余额
        double balance = account.getBalance();
        if (balance < 5000) {
            // 如果余额不足则不能转账
            System.out.println("余额不足！");
            // 释放资源
            qr.getDataSource().getConnection().close();
        } else {
            // 可以转账
            Connection connection = DruidUtils.getConnection();
            // 手动管理事务
            connection.setAutoCommit(false);
            // 编写转账sql: 1122334455的账户-5000
            String sql2 = "update account set balance = balance - 5000 where card = ?";
            // 编写转账记录sql
            String sql4 = "insert into TRANSACTION values(?, ?, ?, ?, ?)";
            String payAccount = "1122334455";
            Date date = new Date();
            Object[] params = { null, payAccount, "decrease", 5000.0, date };
            // 执行转账sql
            qr.update(connection, sql2, payAccount);
            // 执行添加转账记录sql
            qr.update(connection, sql4, params);
            // 模拟异常
//            System.out.println(1 / 0);
            // 编写收款sql
            String sql3 = "update account set balance = balance + 5000 where card = ?";
            String receiveAccount = "55443332211";
            // 编写收款记录sql
            String sql5 = "insert into TRANSACTION values(?, ?, ?, ?, ?)";
            Date date2 = new Date();
            Object[] params2 = { null, payAccount, "increase", 5000.0, date2 };
            // 执行转账sql
            qr.update(connection, sql3, receiveAccount);
            // 执行添加转账记录sql
            qr.update(connection, sql5, params2);
            connection.commit();
        }
    }

    @Test
    public void test2() throws SQLException {
        // 1.自动创建
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 2.编写sql1(价格 > 2000 并且在2019年之前生产的手机)
        String sql = "select * from phone where price > 2000 and prodate < '2019-01-01'";
        // 3.执行sql1
        List<Phone> phone = qr.query(sql, new BeanListHandler<>(Phone.class));
        System.out.println("phone" + phone);

        // 4.编写sql2(查询颜色为白色的手机)
        String sql2 = "select * from phone where color like '白色'";
        List<Phone> phones = qr.query(sql2, new BeanListHandler<>(Phone.class));
        System.out.println("白色手机：" + phones);
    }

    @Test
    public void test3() throws SQLException {
        // 1.自动创建
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 2.编写sql1（查询所有员工信息，不保护没有部门的员工）
        String sql1 = "select * from employee where did is not null";
        // 3.执行sql1
        List<Employee> employees = qr.query(sql1, new BeanListHandler<>(Employee.class));
        System.out.println("1.有部门的员工：" + employees);
        // 4.编写sql2（查询每个员工的 姓名, 薪资 和 所属部门名称）
        String sql2 = "select e.`NAME`, e.`salary`, d.`deptname` from employee e join dept d on e.did = d.id";
        List<Employee> employees2 = qr.query(sql2, new BeanListHandler<>(Employee.class));
        System.out.println("2." + employees2);
    }
}
